﻿using Models.Common;
using Models.MVC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;

namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class Bill5Controller : Controller
    {
        // GET: Bill5
        public ActionResult DetailsList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex5.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex5 left join Goods on BillIndex5.GoodsID=Goods.ID left join Goods as SGoods on BillIndex5.SourceGoodsID=SGoods.ID
WHERE BillIndex5.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill5IndexModel m = new Models.MVC.Bill5IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill5IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string DetailsListAsync(int IndexID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@IndexID",SqlDbType.Int),
            };
            parameters[0].Value = IndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill5BillDetailsList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        // GET: Bill5
        public ActionResult IndexList()
        {
            return View();
        }

        [HttpPost]
        public string IndexListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", int State = 0)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@State",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill5IndexList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexEdit(int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex5.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex5 left join Goods on BillIndex5.GoodsID=Goods.ID left join Goods as SGoods on BillIndex5.SourceGoodsID=SGoods.ID
WHERE BillIndex5.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill5IndexModel m = new Models.MVC.Bill5IndexModel();
            m.CreateDate = DateTime.Now.ToString("yyyy-MM-dd");
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill5IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=4 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            DataTable ddt = FreeADO.DBUtility.DbHelperSQL.Query("select * from DengJi").Tables[0];
            string ds = "";
            foreach (DataRow item in ddt.Rows)
            {
                ds = ds + @"<option value=""" + item["Name"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.DengJi = ds;

            if (ID == 0)
            {
                DataTable mdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillTempletIndex where BillNameID=5 and IsDeleted=0").Tables[0];
                string ms = "";
                foreach (DataRow item in mdt.Rows)
                {
                    ms = ms + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
                }
                ViewBag.MoBan = ms;
            }
            else
            {
                ViewBag.MoBan = "";
            }


            return View();
        }
        [HttpPost]
        public ActionResult IndexEditAsync(Models.MVC.Bill5IndexModel m)
        {
            Result<string> result = Web.Common.Bill5.IndexEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult IndexDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillIndex5 set IsDeleted=1 where ID in (" + IDs + @")");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult ModifyIndexStateAsync(int ID,int State)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                SqlParameter[] paras ={
                    new SqlParameter("@BillNameID",SqlDbType.Int),
                    new SqlParameter("@IndexID",SqlDbType.Int),
                    new SqlParameter("@Type",SqlDbType.Int),
                    new SqlParameter("@Str",SqlDbType.NVarChar,4000),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                                       };
                paras[0].Value = 5;
                paras[1].Value = ID;
                paras[2].Value = State;
                paras[3].Value = "";
                paras[3].Direction = ParameterDirection.InputOutput;
                paras[4].Direction = ParameterDirection.ReturnValue;

                DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ModifyIndexState", paras, "aaa", 3000);
                if (Convert.ToInt32(paras[4].Value) == 1)
                {
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = 0;
                }
                else
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = paras[3].Value.ToString();
                    result.Data = 0;
                }
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillProceduresEdit(int IndexID = 0,int ID=0)
        {
            ViewBag.IndexID = IndexID;
            ViewBag.ID = ID;
            ViewBag.ProcedureID = 0;
            ViewBag.Remark = "";
            ViewBag.ShowIndex = 0;
            ViewBag.BiaoZhunQty = 0;
            ViewBag.GongYiID = 0;
            if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures5 where IsDeleted=0 and BillIndexID=" + IndexID)) > 0)
            {
                ViewBag.ShowIndex = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select max(ShowIndex) from BillProcedures5 where IsDeleted=0 and BillIndexID=" + IndexID)) + 1;
            }

            if (ID > 0)
            {
                DataTable fdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillProcedures5 where ID=" + ID).Tables[0];
                ViewBag.ProcedureID = fdt.Rows[0]["ProcedureID"].ToString();
                ViewBag.Remark = fdt.Rows[0]["Remark"].ToString();
                ViewBag.ShowIndex = fdt.Rows[0]["ShowIndex"].ToString();
                ViewBag.BiaoZhunQty = fdt.Rows[0]["BiaoZhunQty"].ToString();
                ViewBag.GongYiID = fdt.Rows[0]["GongYiID"].ToString();
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Procedures where IsDeleted=0 and State=1 and BillNameID=5").Tables[0];
            string s = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Procedures = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillProceduresEditAsync(int IndexID = 0, int ID = 0, int ProcedureID = 0, string Remark = "",int ShowIndex=0, decimal BiaoZhunQty = 0,int GongYiID=0)
        {
            Result<int> result = new Result<int>();
            if (BiaoZhunQty <= 0)
            {
                result.Type = AjaxResultType.Error;
                result.Content = "工时标准必须大于0！";
                result.Data = 0;
                return Json(result);
            }
            try
            {
                if (ID == 0)
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures5 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures5 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                    }

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("insert into BillProcedures5 (BillIndexID,ProcedureID,Remark,ShowIndex,BiaoZhunQty,GongYiID) values (" + IndexID + "," + ProcedureID + ",@Remark," + ShowIndex + "," + BiaoZhunQty.ToString() + ","+GongYiID+")", new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                else
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures5 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0 and ID<>" + ID)) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures5 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0 and ID<>" + ID);
                    }
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update BillProcedures5 set ProcedureID=" + ProcedureID + ",Remark=@Remark,ShowIndex=" + ShowIndex + @",BiaoZhunQty=" + BiaoZhunQty.ToString() + ", GongYiID=" + GongYiID + " where ID=" + ID, new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                result.Type = AjaxResultType.Success;
                result.Content = "成功。";
                result.Data = 0;

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillDetailsEdit(int BillProcedureID = 0, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillDetails5 where ID=" + ID).Tables[0];
            Bill5DetailsModel m = new Bill5DetailsModel();
            m.BillProcedureID = BillProcedureID;
            m.BiaoZhun= FreeADO.DBUtility.DbHelperSQL.GetSingle("select Remark from BillProcedures5 where ID=" + BillProcedureID).ToString();
            m.InQty = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select dbo.Fn_Value_GetOutQty(5," + BillProcedureID + ")"));
            m.OutQty = m.InQty;
            m.OperatorUserID = Common.CookiesManagement.GetUserModel().UserID;
            m.GongYiID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select GongYiID from BillProcedures5 where ID=" + BillProcedureID));
            m.ProcedureID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select ProcedureID from BillProcedures5 where ID=" + BillProcedureID));
            if (dt.Rows.Count>0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill5DetailsModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                m.BeginDate = Convert.ToDateTime(m.BeginDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.BeginDate).ToString("yyyy-MM-dd");
                m.EndDate = Convert.ToDateTime(m.EndDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.EndDate).ToString("yyyy-MM-dd");
                m.ProcedureID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select ProcedureID from BillProcedures5 where ID=" + BillProcedureID));
            }
            ViewBag.model = m;
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=4 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsEditAsync(Bill5DetailsModel m)
        {
            Result<int> result = Web.Common.Bill5.BillDetailsEdit(m);
            return Json(result);

        }
        [HttpPost]
        public ActionResult BillProceduresDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails5 where BillProcedureID in (" + IDs + @")");
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillProcedures5 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails5 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult BillDetailsStuffList(int BillDetailsID)
        {
            ViewBag.ID = BillDetailsID;
            return View();
        }

        [HttpPost]
        public string BillDetailsStuffListAsync(int BillDetailsID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillDetailsID",SqlDbType.Int),
            };
            parameters[0].Value = BillDetailsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill5DetailsStuffList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BillDetailsStuffEdit(int BillDetailsID,int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select a.Qty,a.GoodsID,b.TypeID,a.ID
FROM BillDetailsStuff5 as a inner join Goods b on a.GoodsID=b.ID
WHERE a.ID=" + ID).Tables[0];
            ViewBag.BillDetailsID = BillDetailsID;
            ViewBag.ID = ID;
            ViewBag.Qty = 0;
            ViewBag.GoodsID = 0;
            ViewBag.GoodsType = 1;
            Models.MVC.Bill5IndexModel m = new Models.MVC.Bill5IndexModel();
            if (dt.Rows.Count > 0)
            {
                ViewBag.Qty = dt.Rows[0]["Qty"].ToString();
                ViewBag.GoodsID = dt.Rows[0]["GoodsID"].ToString();
                ViewBag.GoodsType = dt.Rows[0]["TypeID"].ToString();
            }
            ViewBag.model = m;




            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsStuffEditAsync(int BillDetailsID,int GoodsID,int Qty,int ID)
        {
            Result<string> result = new Result<string>();
            try
            {
                if (ID == 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillDetailsStuff5]
           ([BillDetailsID]
           ,[GoodsID]
           ,[Qty])
     VALUES
           (
'" + BillDetailsID + @"',
'" + GoodsID + @"',
'" + Qty + @"'
)
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
                else
                {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
UPDATE [dbo].[BillDetailsStuff5]
   SET [BillDetailsID] = '" + BillDetailsID + @"'
      ,[GoodsID] = '" + GoodsID + @"'
      ,[Qty] = '" + Qty + @"'
 WHERE ID=" + ID + @"
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsStuffDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetailsStuff5 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult JiaoFuList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex5.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex5 left join Goods on BillIndex5.GoodsID=Goods.ID left join Goods as SGoods on BillIndex5.SourceGoodsID=SGoods.ID
WHERE BillIndex5.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill5IndexModel m = new Models.MVC.Bill5IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill5IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string JiaoFuListAsync(int IndexID)
        {

            GridResult result = new GridResult();
            
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select ID,CONVERT(nvarchar(50),CreateTime,120) as CreateTime,Qty ,Number,Remark
from GoodsStockRunnings where BillNameID=5 and Type=1 and BillIndexID=" + IndexID+@"
order by ID desc").Tables[0];
            result.count =0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult JiaoFuEdit(int IndexID, int ID = 0)
        {
            ViewBag.IndexID = IndexID;
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select * from GoodsStockRunnings WHERE ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.JiaoFuModel m = new Models.MVC.JiaoFuModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.JiaoFuModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateTime = Convert.ToDateTime(m.CreateTime).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult JiaoFuEditAsync(int IndexID, decimal Qty, int ID = 0, string Remark = "", string Number = "", int IsExec = 0, string CreateTime = "")
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            result = Web.Common.BillCommon.JiaoFuEditAsync(5, IndexID, Qty, ID, Remark, Number, IsExec, CreateTime);

            return Json(result);
        }
        [HttpPost]
        public ActionResult ShowIndexEditAsync(int IndexID, int BillProcedureID, int ShowIndex)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures5 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures5 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                }

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillProcedures5 set ShowIndex=" + ShowIndex + " where ID=" + BillProcedureID);
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }
        public ActionResult ReportChengPinLv()
        {
            string TableClo = "";
            string TableCloUp = "";
            DataTable dt =FreeADO.DBUtility.DbHelperSQL.Query("select * from [Procedures] where  [Procedures].BillNameID=5 and IsDeleted=0 ").Tables[0];
            foreach (DataRow item in dt.Rows)
            {
                TableClo = TableClo + @"{ field: 'C_"+item["ID"].ToString()+ @"_OperatorUserName', title: '操作员' , templet: function (d) {  return d.C_" + item["ID"].ToString() + @"_OperatorUserName.substring(1) }},
{ field: 'C_" + item["ID"].ToString() + @"_BeginDate', title: '开始时间' },
{ field: 'C_" + item["ID"].ToString() + @"_EndDate', title: '结束时间' },
{ field: 'C_" + item["ID"].ToString() + @"_TaskTime', title: '工时（分钟）' },";
                TableCloUp = TableCloUp + @"{ field: '', title: '" + item["Name"].ToString() + @"' ,colspan:4,align:'center' },";
            }
            ViewBag.TableClo = TableClo;
            ViewBag.TableCloUp = TableCloUp;
            //            ViewBag.TableClo = FreeADO.DBUtility.DbHelperSQL.GetSingle(@"select '{ field: ''C_'+Convert(nvarchar(50),ID)+'_OperatorUserName'', title: ''操作员'' , templet: function (d) {  return d.C_'+Convert(nvarchar(50),ID)+'_OperatorUserName.substring(1) }},'
            //+'{ field: ''C_'+Convert(nvarchar(50),ID)+'_BeginDate'', title: ''开始时间'' },'
            //+'{ field: ''C_'+Convert(nvarchar(50),ID)+'_EndDate'', title: ''结束时间'' },'
            //+'{ field: ''C_'+Convert(nvarchar(50),ID)+'_TaskTime'', title: ''工时（分钟）'' },'
            //from  [Procedures] where  [Procedures].BillNameID=5 and IsDeleted=0 for xml path('')").ToString();
            //            ViewBag.TableCloUp = FreeADO.DBUtility.DbHelperSQL.GetSingle(@"select '{ field: '''', title: '''+Name+''' ,colspan:4,align:''center'' },'
            //from  [Procedures] where  [Procedures].BillNameID=5 and IsDeleted=0 for xml path('')").ToString();
            return View();
        }

        [HttpPost]
        public string ReportChengPinLvAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = BeginDate;
            parameters[4].Value = EndDate;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill5ReportTaskTime", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public ActionResult ReportYear()
        {
            return View();
        }

        [HttpPost]
        public string ReportYearAsync(int PageIndex = 1, int PageSize = 1000000000, string Year = "")
        {
            try
            {
                DateTime a = DateTime.Parse(Year + "-01-01");
            }
            catch 
            {
                Year = DateTime.Now.Year.ToString();
            }
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Year",SqlDbType.NVarChar,40000),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Year;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill5IndexReportYear", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        [HttpPost]
        public ActionResult ProShowIndexUpdateAsync(int ID ,int ShowIndex)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Update BillProcedures5 set ShowIndex="+ShowIndex+" where ID ="+ID);
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }

        public FileResult Down(int ID)
        {

            int JiHuaOrderID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select JiHuaOrderID from BillIndex3 where ID=" + ID));
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select pro.Name as ProName
	, isnull((select CheckRemark+''from BillDetails5 as bdd where bdd.BillProcedureID=bp.ID for xml path('')),'') as  CheckRemark
	,isnull(sum(InQty),0) as InQty,isnull(sum(OutQty),0) as OutQty,isnull(sum(FailQty),0) as FailQty
	,isnull(Convert(nvarchar(10),max(EndDate),120),'') as EndDate
	,isnull((select isnull(Users.Name,'')+' 'from BillDetails5 as bdd left join Users on bdd.OperatorUserID=users.ID where bdd.BillProcedureID=bp.ID for xml path('')),'')+'
'+isnull(Convert(nvarchar(10),max(EndDate),120),'') as UserName
	, isnull((select Remark+''from BillDetails5 as bdd where bdd.BillProcedureID=bp.ID for xml path('')),'') as  Remark
    ,isnull((select isnull(Users.Name,'')+' 'from BillDetails5 as bdd left join Users on bdd.CheckUserID=users.ID where bdd.BillProcedureID=bp.ID for xml path('')),'')+'
'+case when sum(CheckUserID)>0 then Convert(nvarchar(10),max(CheckDate),120) else '' end as CheckUserName
	,bp.ShowIndex
	,case when sum(CheckUserID)>0 then Convert(nvarchar(10),max(CheckDate),120) else '' end as CheckDate
from BillProcedures5 as bp left join BillDetails5 as bd on bd.BillProcedureID=bp.ID
	left join [Procedures] as pro on bp.ProcedureID=pro.ID
	left join Users as u on bd.OperatorUserID=u.ID
    left join Users as cu on bd.CheckUserID=cu.ID
where bp.BillIndexID=" + ID + @"
group by pro.Name,ShowIndex,bp.ID,bp.ShowIndex 
order by bp.ShowIndex
").Tables[0];

            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select bi.ID,bi.Code,bi.Number,bi.GoodsNumber,CONVERT(nvarchar(10),CreateDate,120) as CreateDate,bi.Qty
		,g.Model as GoodsModel,g.Name as GoodsName,g.Code as GoodsCode
from BillIndex5 as bi inner join Goods as g on bi.GoodsID=g.ID
where bi.ID=" + ID).Tables[0];
            Models.MVC.Bill5IndexModel m = new Models.MVC.Bill5IndexModel();
            if (dt1.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill5IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
            }
            MemoryStream ms = Common.ExcelTool.Bill5ToExcel(dt, m);
            return File(ms, "application/vnd.ms-excel", m.Code + ".xls");
        }

    }
}